﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;



namespace AutoUpdater
{
    public class DBTransactions
    {
        //initializers

        MySqlConnection conn;
        

        public DBTransactions()
        {

  

        }//default constructor

        //returns 0 for success
        public int OpenDB()
        {
            string ConnectionString = "server=localhost;uid=root;password=slang;database=webtraderlite;port=3306";
            conn = new MySqlConnection(ConnectionString);
            //conn.ConnectionString = "Driver={MySQL ODBC 5.2w Driver};server=localhost;uid=root;password=slang;database=webtraderlite;port=3306";
            
            try
            {
                conn.Open();
                // Process data here.
                //Console.WriteLine("connection sucessfull");
                Console.WriteLine("conn successful");
                return 0;
            }
            catch (Exception ex)
            {
                //Console.WriteLine("Failed to connect to data source" + ex);
                Console.WriteLine("conn unsuccessful" + ex);
                return 1;
            }
        }//end OpenDB

        public int CloseDB()
        {

            try
            {
                conn.Close();
                Console.WriteLine("conn Closed successful");
                return 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine("conn Closed unsuccessful" + ex);
                return 1;
            } 
        }//end CloseDB




        public MySqlDataReader Query(string Query)
        {
            this.OpenDB();
            MySqlDataReader reader = null;
            try
            {
                MySqlCommand cmd = new MySqlCommand(Query, conn);
                reader = cmd.ExecuteReader();
                return reader;
                
            }//end try
            catch(MySqlException ex)
            {
                Console.WriteLine(ex);
            }//end catch

            this.CloseDB();
            return null;

        }//end Query

       
        public List<AutoSell> getAllAutoSellStocks()
        {

            List<AutoSell> mysell=new List<AutoSell>();
            string SQL = "select userstockid, sellPrice, noofShare,id  from autosell";
            MySqlDataReader result;
            this.OpenDB();
            MySqlCommand cmdx = new MySqlCommand(SQL, conn);
            result = cmdx.ExecuteReader();
            while (result.Read())
            {
                AutoSell sell = new AutoSell(result.GetFloat(1), result.GetInt32(2), result.GetInt32(0), result.GetString(3));
                mysell.Add(sell);
            }
            this.CloseDB();
            this.OpenDB();
            int allsell=mysell.Count;
            for (int i = 0; i <allsell; i++)
            {
                string sql = "select loginname,stocksymbol from userstock where userstockid="+mysell.ElementAt(i).getuserstockid();
                MySqlDataReader result2;
                MySqlCommand cmdy = new MySqlCommand(sql, conn);
                result2 = cmdy.ExecuteReader();
                int count = 0;
                while (result2.Read())
                {
                    count++;
                    if (count > 1)
                    {
                        AutoSell sell = new AutoSell(mysell.ElementAt(i).getPrice(), mysell.ElementAt(i).getShare(), mysell.ElementAt(i).getuserstockid(), mysell.ElementAt(i).getID());
                        sell.setSymbol(result2.GetString(1));
                        sell.setUsername(result2.GetString(0));
                        mysell.Add(sell);
                    }
                    else
                    {
                        mysell.ElementAt(i).setSymbol(result2.GetString(1));
                        mysell.ElementAt(i).setUsername(result2.GetString(0));
                    }
                }
            }
            this.CloseDB();
            return mysell;
        }
        public bool updateStokAfterSell(User usr, Stock stk, float no)
        {
            this.OpenDB();
            string Sql = "update userstock set noofshare=noofshare-" + (Int32)(no) + "  where loginname='" + usr.getUserID() +
                "' and " + "stockSymbol='" + stk.Symbol.ToUpper() + "'";
            try
            {
                MySqlCommand cmd = new MySqlCommand(Sql, conn);
                cmd.ExecuteNonQuery();
                this.CloseDB();
                return true;
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            this.CloseDB();
            return false;
        }

        public void creditBalance(User usr, Stock stck, float no)
        {
            this.OpenDB();
            string SQL = "update user set accBalance=accBalance+" + (stck.Price * no) + "where loginname='" + usr.getUserID() + "'";
            try
            {
                MySqlCommand cmd = new MySqlCommand(SQL, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            this.CloseDB();
        }




     
      public void insertIntoHistory(User usr, Stock stk, float p, string eventx)
      {
          this.OpenDB();
          string SQL = "insert into history(loginName, historyType, stock, numShares, price, dateofEvent) values('" +
              usr.getUserID() + "','" + eventx + "', '" + stk.Symbol.ToUpper() + "'," + (Int32)(stk.SharesOutstanding) + "," + p + ",now()" + ")";
          try
          {
              MySqlCommand cmd = new MySqlCommand(SQL, conn);
              cmd.ExecuteNonQuery();
          }
          catch (Exception ex)
          {
              Console.Write(ex);
          }
          this.CloseDB();
      }


      public void deleteFromAutoSell(AutoSell sellx)
      {
          this.OpenDB();
          string SQL = "delete from AutoSell where id='" + sellx.getID()+"'";
          MySqlCommand cmd;
          try
          {
              cmd = new MySqlCommand(SQL, conn);
              cmd.ExecuteNonQuery();
          }
          catch (Exception ex)
          {
              Console.WriteLine(ex);
          }
          this.CloseDB();
      }

     public List<AutoBuy> getAllAutoBuyStocks()
      {
          string SQL = "select loginName, stockSymbol, purchasePrice, noofShare, id from autobuy";
          MySqlDataReader result;
          result=Query(SQL);
          List<AutoBuy> buy = new List<AutoBuy>();
          while (result.Read())
          {
              AutoBuy buyt = new AutoBuy(result.GetString(4), result.GetString(1), result.GetString(0), result.GetInt32(3), result.GetFloat(2));
              buy.Add(buyt);
          }
          return buy;
      }

     public float retrieveAccountBal(string userID)
     {
         this.OpenDB();
         String SQL = "Select accBalance from User where loginName= '" + userID + "'";
         MySqlDataReader result;
         result = Query(SQL);

         while (result.Read())
         {
             return result.GetFloat(0);
         }
         this.CloseDB();
         return (float)0.0;


     }//end retrieveAccountBal
     public bool inIntoUserStock(User usr, Stock stk, float no)
     {


         string sql = "select loginname from userstock where loginname='" + usr.getUserID() + "' and stockSymbol='" + stk.Symbol.ToUpper() + "'";
         try
         {
             //check wheter this stock exists, if exists then update otherwise insert.
             MySqlDataReader result;
             result = Query(sql);
             if (result.HasRows)
             {
                 this.OpenDB();
                 string Sql = "update userstock set noofshare=noofshare+" + (Int32)(no) + "  where loginname='" + usr.getUserID() +
                     "' and " + "stockSymbol='" + stk.Symbol.ToUpper() + "'";
                 MySqlCommand cmd = new MySqlCommand(Sql, conn);
                 cmd.ExecuteNonQuery();
                 this.CloseDB();
                 return true;
             }
             else
             {
                 this.OpenDB();
                 string SQL = "insert into userstock(loginName,stockSymbol,lastpurchasePrice, noofShare) values" +
                               "('" + usr.getUserID() + "','" + stk.Symbol.ToUpper() + "'," + stk.Price + "," + (Int32)(no) + ")";
                 try
                 {
                     MySqlCommand cmd = new MySqlCommand(SQL, conn);
                     cmd.ExecuteNonQuery();
                 }
                 catch (Exception ex)
                 {
                     Console.Write(ex);
                 }
                 this.CloseDB();
                 return true;
             }

         }
         catch (Exception ex)
         {
             Console.Write("ourside userstock insert" + ex);
         }
         this.CloseDB();
         return false;
     }

     public void debitBalance(User usr, Stock stck, float no)
     {
         this.OpenDB();
         string SQL = "update user set accBalance=accBalance-" + (stck.Price * no) + "where loginname='" + usr.getUserID() + "'";
         try
         {
             MySqlCommand cmd = new MySqlCommand(SQL, conn);
             cmd.ExecuteNonQuery();
         }
         catch (Exception ex)
         {
             Console.Write(ex);
         }
         this.CloseDB();
     }

     public void removeFromAutoBuy(AutoBuy autoBuy)
     {
         string SQL = "delete from autobuy where id='" + autoBuy.getid()+"'";
         this.OpenDB();
         MySqlCommand cmd=new MySqlCommand(SQL, conn);
         try
         {
             cmd.ExecuteNonQuery();
         }
         catch (Exception ex)
         {
             Console.Write(ex);
         }
         this.CloseDB();
     }

     public  void DeletefromUserStock()
     {
         this.OpenDB();
         string SQL = "delete from userstock where noofshare =" + 0;
         MySqlCommand cmd = new MySqlCommand(SQL, conn);
         try
         {
             cmd.ExecuteNonQuery();
             this.CloseDB();
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex);
         }
     }
     public float getBrokerFee()
     {
         this.OpenDB();

         String SQL = "SELECT fee FROM fees where ChangeDate = (Select Max(ChangeDate) from fees)";
         MySqlDataReader result;
         result = Query(SQL);

         while (result.Read())
         {
             float d = result.GetFloat(0);
             this.CloseDB();
             return d;
         }
         this.CloseDB();
         return (float)0.0;

     }
     public void deductandAddbrokerFees(string user)
     {
         float d = getBrokerFee();

         this.OpenDB();
         string SQL = "update user set accBalance=accBalance-" + d + " where loginName='" + user + "'";
         MySqlCommand cmd = new MySqlCommand(SQL, conn);
         try
         {
             cmd.ExecuteNonQuery();
             string sql = "update user set accBalance=accBalance+" + d + " where loginName= '" + "john" + "'";
             MySqlCommand cmdy = new MySqlCommand(sql, conn);
             cmdy.ExecuteNonQuery();

         }
         catch (Exception ex)
         {
             Console.WriteLine(ex);
         }
         this.CloseDB();

     }






    }//end Class DBTransactions
}